package cn.just.dao;

import cn.just.entity.Article;
import cn.just.entity.Tag;
import cn.just.utils.JDBCUtil;
import jdk.nashorn.internal.scripts.JD;

import javax.sql.rowset.JdbcRowSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wujiaming
 */
public class ArticleDao {

    /**
     * 最新8篇置顶文章
     * @return
     * @throws SQLException
     */
    public List<Article> getHomeList() throws SQLException {
        List<Article> articles = new ArrayList<Article>();
        Connection connect = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connect = JDBCUtil.getConnect();
        String sql = "select a.oId,a.title,a.abstract,u.userName,a.commentCount,a.viewCount,a.putTop,a.created,a.updated,a.imgName " +
                "from article a join user u " +
                "on authorId=u.oId " +
                "order by a.putTop desc,a.created desc " +
                "limit 0,8";

        preparedStatement = connect.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int oId = resultSet.getInt("oId");
            String title = resultSet.getString("title");
            String anAbstract = resultSet.getString("abstract");
            String username = resultSet.getString("username");
            int commentCount = resultSet.getInt("commentCount");
            int viewCount = resultSet.getInt("viewCount");
            int putTop = resultSet.getInt("putTop");
            long created = resultSet.getLong("created");
            long updated = resultSet.getLong("updated");
            String imgName = resultSet.getString("imgName");

            Article article = new Article(oId, title, anAbstract, username,
                    commentCount, viewCount, putTop, created, updated, imgName);

            //查询文章id对应的标签，让他们关联
            TagDao tagDao = new TagDao();
            List<Tag> tags = tagDao.findTagByArticleId(oId);
            article.setTags(tags);
            articles.add(article);
        }

        JDBCUtil.close(connect,preparedStatement,resultSet);
        return articles;
    }

    /**
     * 查询最新的5篇文章
     * @return
     * @throws SQLException
     */
    public List<Article> getNewList() throws SQLException {
        List<Article> articles = new ArrayList<Article>();
        Connection connect = null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        connect = JDBCUtil.getConnect();
        String sql = "select a.title,a.created " +
                "from article a " +
                "order by created desc " +
                "limit 0,5";
        preparedStatement = connect.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String title = resultSet.getString("title");
            String created = resultSet.getString("created");
            Article article = new Article();
            article.setTitle(title);
            article.setCreated(Long.parseLong(created));
            articles.add(article);
        }

        JDBCUtil.close(connect,preparedStatement,resultSet);
        return articles;
    }

    /**
     * 查询评论最热的五篇文章
     * @return
     * @throws SQLException
     */
    public List<Article> getMaxCommentArticle() throws SQLException {
        ArrayList<Article> articles = new ArrayList<Article>();
        Connection connect = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        connect = JDBCUtil.getConnect();
        String sql = "select a.title,a.commentCount " +
                "from article a " +
                "order by commentCount desc " +
                "limit 0,5";
        preparedStatement = connect.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String title = resultSet.getString("title");
            String commentCount = resultSet.getString("commentCount");
            Article article = new Article();
            article.setTitle(title);
            article.setCommentCount(Integer.parseInt(commentCount));
            articles.add(article);
        }
        JDBCUtil.close(connect,preparedStatement,resultSet);
        return articles;
    }

    /**
     * 查询浏览最多的5篇文章
     * @return
     * @throws SQLException
     */
    public List<Article> getMaxViewArticle() throws SQLException {
        ArrayList<Article> articles = new ArrayList<Article>();
        Connection connect = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        connect = JDBCUtil.getConnect();

        String sql = "select title,viewCount " +
                "from article " +
                "order by viewCount desc " +
                "limit 0,5";
        preparedStatement = connect.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String title = resultSet.getString("title");
            String viewCount = resultSet.getString("viewCount");
            Article article = new Article();
            article.setTitle(title);
            article.setViewCount(Integer.parseInt(viewCount));
            articles.add(article);
        }
        JDBCUtil.close(connect,preparedStatement,resultSet);
        return articles;
    }


    /**
     * 分页查询；查询当前页有哪些数据
     * @param pageNo 当前页的页号
     * @return 返回当前页的文章
     * @throws SQLException
     */
    public List<Article> getArticleList(int pageNo) throws SQLException {
        List<Article> articles = new ArrayList<Article>();
        Connection connect = null;
        ResultSet resultSet = null;

        connect = JDBCUtil.getConnect();
        String sql = "select a.oId,a.title,a.abstract,u.userName,a.commentCount,a.viewCount,a.putTop,a.created,a.updated,a.imgName " +
                "from article a join user u " +
                "on authorId=u.oId " +
                "order by a.created desc " +
                "limit ?,8";

        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        preparedStatement.setInt(1,(pageNo-1)*8);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int oId = resultSet.getInt("oId");
            String title = resultSet.getString("title");
            String anAbstract = resultSet.getString("abstract");
            String username = resultSet.getString("username");
            int commentCount = resultSet.getInt("commentCount");
            int viewCount = resultSet.getInt("viewCount");
            int putTop = resultSet.getInt("putTop");
            long created = resultSet.getLong("created");
            long updated = resultSet.getLong("updated");
            String imgName = resultSet.getString("imgName");

            Article article = new Article(oId, title, anAbstract, username,
                    commentCount, viewCount, putTop, created, updated, imgName);

            //查询文章id对应的标签，让他们关联
            TagDao tagDao = new TagDao();
            List<Tag> tags = tagDao.findTagByArticleId(oId);
            article.setTags(tags);
            articles.add(article);
        }

        JDBCUtil.close(connect,preparedStatement,resultSet);
        return articles;
    }

    /**
     * 查询文章的总数量,用于计算页码数
     * @return
     * @throws SQLException
     */
    public int selectCount() throws SQLException {
        int count = 0;
        Connection connect = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connect = JDBCUtil.getConnect();
        String sql = "select count(1) c" +
                    " from article a  join user u" +
                    " on a.authorId=u.oId";
        preparedStatement = connect.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            count = resultSet.getInt("c");
        }

        JDBCUtil.close(connect,preparedStatement,resultSet);
        return count;
    }

    /**
     * 根据标题查询文章
     * @param keywords
     * @return
     * @throws SQLException
     */
    public List<Article> findArticleByKeyWord(String keywords) throws SQLException {
        ArrayList<Article> articles = new ArrayList<Article>();

        Connection connect =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;

        connect = JDBCUtil.getConnect();
        String sql = "select a.oId,a.title,a.abstract,u.userName,a.commentCount,a.viewCount,a.putTop,a.created,a.updated,a.imgName " +
                "from article a join user u " +
                "on authorId=u.oId " +
                "where a.title like ? "+
                "order by a.created desc ";
        preparedStatement = connect.prepareStatement(sql);
        preparedStatement.setString(1,"%" + keywords + "%");
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int oId = resultSet.getInt("oId");
            String title = resultSet.getString("title");
            String anAbstract = resultSet.getString("abstract");
            String username = resultSet.getString("username");
            int commentCount = resultSet.getInt("commentCount");
            int viewCount = resultSet.getInt("viewCount");
            int putTop = resultSet.getInt("putTop");
            long created = resultSet.getLong("created");
            long updated = resultSet.getLong("updated");
            String imgName = resultSet.getString("imgName");

            Article article = new Article(oId, title, anAbstract, username,
                    commentCount, viewCount, putTop, created, updated, imgName);

            //查询文章id对应的标签，让他们关联
            TagDao tagDao = new TagDao();
            List<Tag> tags = tagDao.findTagByArticleId(oId);
            article.setTags(tags);
            articles.add(article);
        }

        JDBCUtil.close(connect,preparedStatement,resultSet);
        return articles;
    }

    /**
     * 根据文章Oid查询指定文章
     * @param oId
     * @return
     * @throws SQLException
     */
    public Article findById(int oId) throws SQLException {
        Connection connect =null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Article article = null;

        connect = JDBCUtil.getConnect();
        String sql = "select a.oId,a.title,a.abstract,u.userName,a.commentCount," +
                "a.viewCount,a.putTop,a.created,a.updated,a.imgName,a.content " +
                "from article a join user u " +
                "on authorId=u.oId " +
                "where a.oId=? ";

        preparedStatement = connect.prepareStatement(sql);
        preparedStatement.setInt(1,oId);
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            int id = resultSet.getInt("oId");
            String title = resultSet.getString("title");
            String anAbstract = resultSet.getString("abstract");
            String username = resultSet.getString("username");
            int commentCount = resultSet.getInt("commentCount");
            int viewCount = resultSet.getInt("viewCount");
            int putTop = resultSet.getInt("putTop");
            long created = resultSet.getLong("created");
            long updated = resultSet.getLong("updated");
            String imgName = resultSet.getString("imgName");
            String content = resultSet.getString("content");
            article = new Article(id,title,anAbstract,username,commentCount,
                    viewCount,putTop,created,updated,imgName);
            //把查询到的文章和article关联
            article.setContent(content);

            //查询文章id对应的标签，让他们关联
            TagDao tagDao = new TagDao();
            List<Tag> tags = tagDao.findTagByArticleId(oId);
            article.setTags(tags);
        }
        JDBCUtil.close(connect,preparedStatement,resultSet);
        return article;
    }


    /**
     * 添加文章
     * @param article 前端传进来的文章
     * @throws SQLException
     */
    public void save(Article article) throws SQLException {
        Connection connect = null;
        PreparedStatement preparedStatement =null;

        connect = JDBCUtil.getConnect();
        String sql = "insert into article(title,abstract,authorId,commentCount,viewCount," +
                "content,putTop,created,imgName) " +
                "values(?,?,?,0,0,?,?,?,?)";
        preparedStatement = connect.prepareStatement(sql);
        preparedStatement.setString(1,article.getTitle());
        preparedStatement.setString(2,article.getAbs());
        preparedStatement.setInt(3,article.getAuthorId());
        preparedStatement.setString(4,article.getContent());
        preparedStatement.setInt(5,article.getPutTop());
        preparedStatement.setLong(6,System.currentTimeMillis());
        preparedStatement.setString(7,article.getImgName());
        preparedStatement.executeUpdate();

        JDBCUtil.close(connect,preparedStatement);
    }
}
